//------------------------------------------------------------------------------
// <copyright file="CSSqlStoredProcedure.cs" company="Microsoft">
//     Copyright (c) Microsoft Corporation.  All rights reserved.
// </copyright>
//------------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Diagnostics;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void spIO_GetVolumeSpace(string serverName)
    {
        // See:  http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx
        if (serverName.Equals(string.Empty))
            serverName = Environment.MachineName;

        PerformanceCounterCategory pcc = new PerformanceCounterCategory("LogicalDisk", serverName);

        SqlDataRecord record = new SqlDataRecord(
            new SqlMetaData("Drive/MountPoint", SqlDbType.NVarChar, 256),
            new SqlMetaData("Capacity (MB)", SqlDbType.VarChar, 256),
            new SqlMetaData("Used Space (MB)", SqlDbType.VarChar, 256),
            new SqlMetaData("Free Space (MB)", SqlDbType.VarChar, 256),
            new SqlMetaData("Percent Free Space", SqlDbType.VarChar, 6)
        );

        SqlContext.Pipe.SendResultsStart(record);

        foreach (string instanceName in pcc.GetInstanceNames())
        {
            // Only want to see the values for "Total"
            if (instanceName != "_Total")
            {
                // Get a new object reference to the desired performance counters
                PerformanceCounter pcPercentFree = new PerformanceCounter("LogicalDisk", "% Free Space", instanceName, serverName);
                PerformanceCounter pcFreeMbytes = new PerformanceCounter("LogicalDisk", "Free Megabytes", instanceName, serverName);

                // Get the performance counter values
                float percentfree = pcPercentFree.NextValue();
                float freespace = pcFreeMbytes.NextValue();
                float capacity = (freespace * 100) / percentfree;
                float usedspace = capacity - freespace;

                // Close and free resources
                pcPercentFree.Close();
                pcPercentFree.Dispose();
                pcFreeMbytes.Close();
                pcFreeMbytes.Dispose();

                // Place values into the pipe to return to the caller
                record.SetSqlString(0, instanceName);
                record.SetSqlString(1, capacity.ToString());
                record.SetSqlString(2, usedspace.ToString());
                record.SetSqlString(3, freespace.ToString());
                record.SetSqlString(4, percentfree.ToString());
                SqlContext.Pipe.SendResultsRow(record);
            }
        }
        SqlContext.Pipe.SendResultsEnd();
    }
}
